-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
USE StudentAttendanceChecking
GO
-- =============================================
-- Author:		<Tuan Anh,,Doan>
-- Create date: <22/12/2011,,>
-- Description:	<Show list view for id or select full information,,>
-- =============================================

-- View Branch
CREATE PROCEDURE Get_Branches
AS
BEGIN
	select Branch.BranchID, Branch.BranchName from Branch
END
go

CREATE PROCEDURE Get_Classes @BranchID int
AS
BEGIN
	select ClassID,ClassName,UsersID,BranchID,TimeSlot,StartDay,EndDay,ClassStatus from Class where BranchID=@BranchID
END
GO

-- dua ra danh sach sinh vien theo FAT and Class
CREATE PROCEDURE View_All_Student_Branch_Class @ClassID int 
AS
BEGIN
	SELECT	dbo.Student.StudentID, dbo.Student.Name, dbo.Student.CodeTrainingName, dbo.Student.CurrentClassID, dbo.Student.Gender, dbo.Student.BirthDay				, dbo.Student.Addresss, dbo.Student.Phone, dbo.Student.Email, dbo.Student.StudentStatus, dbo.Student.Descriptions
	FROM         dbo.Student INNER JOIN
                      dbo.Class ON dbo.Student.CurrentClassID = dbo.Class.ClassID
	WHERE     (dbo.Class.ClassID = @ClassID)	
END
GO
-- dua ra danh sach sinh vien theo cac FAT
CREATE PROCEDURE View_Student_Branch @BranchID int
AS
BEGIN
	SELECT     dbo.Student.StudentID, dbo.Student.Name, dbo.Student.Gender, dbo.Student.CurrentClassID, dbo.Branch.BranchName, dbo.Class.TimeSlot, dbo.Class.StartDay, dbo.Class.EndDay
	FROM         dbo.Branch INNER JOIN
                      dbo.Class ON dbo.Branch.BranchID = dbo.Class.BranchID INNER JOIN
                      dbo.Student ON dbo.Class.ClassID = dbo.Student.CurrentClassID
	WHERE     (dbo.Branch.BranchID = @BranchID)
END
GO
--Dua ra danh sach sinh vien theo Class
CREATE PROCEDURE view_Student_Class @ClassID int
AS
BEGIN
	SELECT     dbo.Student.StudentID, dbo.Student.Name, dbo.Student.Gender, dbo.Student.CurrentClassID, dbo.Branch.BranchName, dbo.Class.TimeSlot, dbo.Class.StartDay, dbo.Class.EndDay
	FROM         dbo.Branch INNER JOIN
                      dbo.Class ON dbo.Branch.BranchID = dbo.Class.BranchID INNER JOIN
                      dbo.Student ON dbo.Class.ClassID = dbo.Student.CurrentClassID
	WHERE     (dbo.Class.ClassID = @ClassID)	
END
-- proc hien thi sv, phan tram di hoc
GO
create proc spViewStudent
	@BranchID int,
	@ClassID int,
	@SubjectCode varchar(10)
as

select Student.StudentID,Name, Subjects.SubjectCode,Class.ClassName, COUNT( AttendantsDetails.AttendantsStatusW) as [Total Presence W],Subjects.SubjectTotalW,(COUNT( AttendantsDetails.AttendantsStatusW)*100)/(Subjects.SubjectTotalW) as PercentsW ,COUNT( AttendantsDetails.AttendantsStatusP) as [Total Presence P],Subjects.SubjectTotalP, (COUNT( AttendantsDetails.AttendantsStatusP)*100)/(Subjects.SubjectTotalP) as PercentsP 
from Student inner join AttendantsDetails on AttendantsDetails.StudentID= Student.StudentID
inner join Attendants on Attendants.AttendantsID= AttendantsDetails.AttendantsID 
inner join Subjects on Subjects.SubjectCode=Attendants.SubjectCode
inner join Class on Class.ClassID= Attendants.ClassID
inner join Branch on Branch.BranchID=Class.BranchID

where (AttendantsDetails.AttendantsStatusW=1 or AttendantsDetails.AttendantsStatusW=2 or AttendantsDetails.AttendantsStatusP=1 or AttendantsDetails.AttendantsStatusP=2) and Class.ClassID=@ClassID and Subjects.SubjectCode=@SubjectCode and Branch.BranchID=@BranchID
group by AttendantsDetails.StudentID,Student.StudentID,Name, Subjects.SubjectCode,Class.ClassID,Subjects.SubjectTotalP,Class.ClassName,Subjects.SubjectTotalW
GO
--create procedure to display class by branch
create procedure spGetClassByBranch(@branchID int)
as
	select ClassID,ClassName,UsersID,BranchID,TimeSlot,StartDay,EndDay,ClassStatus from Class where BranchID = @branchID;
GO
-- insert branch
create proc spInsert_Branch
	@BranchName nvarchar(50)
as
insert into Branch(BranchName) values(@BranchName)

GO
--update Branch
create proc spUpdate_Branch
	@BranchID int,
	@BranchName nvarchar(50)
as
	update Branch set BranchName=@BranchName where BranchID=@BranchID
	
	



